﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gfw.Models.Views;
using Gfw.Repository;

namespace Gfw.Repositories.Views
{
    public class UserCountRepository : Repository<UserCount>
    {
        private const string SqlFromat =
            @"select old.*,new.NewLeftChildren,new.NewRightChildren from 
(
select A.ID,A.Code,count(if(LOCATE(CONCAT(Code,'0'), ChildCode)=1, true, null)) as OldLeftChildren
,count(if(LOCATE(CONCAT(Code,'1'), ChildCode)=1, true, null)) as OldRightChildren
from 
(select ID,Code from usr_user) as A 
left join
(select Code as ChildCode,ActivationDate from usr_user where ActivationDate <:start) B
on LOCATE(a.Code, b.ChildCode)=1 and a.Code<>b.ChildCode group by ID,Code
) as old
left join 
(
select A.ID,count(if(LOCATE(CONCAT(Code,'0'), ChildCode)=1, true, null)) as NewLeftChildren
,count(if(LOCATE(CONCAT(Code,'1'), ChildCode)=1, true, null)) as NewRightChildren
from 
(select ID,Code from usr_user) as A 
left join
(select Code as ChildCode,ActivationDate from usr_user where ActivationDate >:start and ActivationDate<=:end ) B
on LOCATE(a.Code, b.ChildCode)=1 and a.Code<>b.ChildCode group by ID
) as new
on old.ID=new.ID
";

        public IList<UserCount> Query(DateTime start,DateTime end)
        {

            var query =
                Session.CreateSQLQuery(SqlFromat).AddEntity(typeof (UserCount)).SetDateTime("end", end).SetDateTime(
                    "start", start);
            return query.List<UserCount>();
        }
    }
}
